package com.dubbo.mapper;

import com.dubbo.domain.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 订单mapper
 * @author chenluqiang
 * @date 2018年12月15日 下午4:35:59 
 *
 */
public interface OrderMapper extends Mapper<Order>{
	
	/**
	 * 根据订单id查询关联人
	 * @author chenluqiang
	 * @date 2018年12月17日 上午11:51:03 
	 * @param orderid
	 * @return
	 */
	@Select("SELECT " + 
			"	oor.linkName " + 
			"FROM " + 
			"	o_order oor " + 
			"WHERE " + 
			"	oor.id = #{orderId}")
	String getLinkNameByOrderId(@Param("orderId") String orderid);
	

	/**
	 * 根据订单id获取退货信息
	 * @author chenluqiang
	 * @date 2018年12月15日 下午5:00:20 
	 * @param orderId
	 * @return
	 */
	@Select("SELECT " + 
			"	oor.id AS orderId, " + 
			"	oor.orderNum, " + 
			"	ooi.id AS orderItemId, " + 
			"	ooi.goodsId, " + 
			"	ooi.goodsPic, " + 
			"	ooi.goodsCount, " + 
			"	ooi.goodsTitle, " + 
			"IF ( " + 
			"	ooi.categoryId = 0 " + 
			"	OR ISNULL(ooi.categoryId), " + 
			"	ggs.categoryId, " + 
			"	ooi.categoryId " + 
			") AS categoryId, " + 
			" ooi.goodsPrice " + 
			"FROM " + 
			"	o_order oor " + 
			"LEFT JOIN o_order_item ooi ON oor.id = ooi.orderId " + 
			"LEFT JOIN g_goods ggs ON ggs.id = ooi.goodsId " + 
			"	WHERE oor.id = #{orderId}")
	List<Map<String, Object>> getReturnGoodsByOrderId(@Param("orderId") String orderId);
	
	/**
	 * 根据订单id获取换货信息
	 * @author chenluqiang
	 * @date 2018年12月15日 下午5:00:46 
	 * @param orderId
	 * @return
	 */
	@Select("SELECT " + 
			"	oor.id AS orderId, " + 
			"	oor.orderNum, " + 
			"	ooi.id AS orderItemId, " + 
			"	ooi.goodsId, " + 
			"	ooi.goodsCount, " + 
			"	ooi.goodsPic, " + 
			"	ooi.goodsPrice, " + 
			"IF ( " + 
			"	ooi.categoryId = 0 " + 
			"	OR ISNULL(ooi.categoryId), " + 
			"	ggs.categoryId, " + 
			"	ooi.categoryId " + 
			") AS categoryId, " + 
			"	ooi.goodsTitle " + 
			"FROM " + 
			"	o_order oor " + 
			"LEFT JOIN o_order_item ooi ON oor.id = ooi.orderId "+
			"LEFT JOIN g_goods ggs ON ggs.id = ooi.goodsId " + 
			"	WHERE oor.id = #{orderId}")
	List<Map<String, Object>> getExchangeGoodsByOrderId(@Param("orderId") String orderId);
	
	/**
	 * 根据订单状态 获取订单列表
	 * @author chenluqiang
	 * @date 2018年12月18日 下午5:04:47 
	 * @param userId
	 * @param type 
	 * @param pageStart
	 * @param pageTotal
	 * @return
	 */
	List<Map<String, Object>> getOrderListByStatus(@Param("userId") String userId, @Param("type") Integer type
            , @Param("pageStart") Long pageStart, @Param("pageTotal") Long pageTotal
            , @Param("userType") Integer userType);
	
	/**
	 * 根据订单状态 统计 列表记录数
	 * @author chenluqiang
	 * @date 2018年12月17日 下午4:24:08 
	 * @param userId
	 * @param type  类型 0=新订单 1=未接单 2=审核中 3=待发货 4=待收货 5=已完成
	 * @return
	 */
	Long getOrderListCountByStatus(@Param("userId") String userId, @Param("type") Integer type, @Param("userType") Integer userType);


	/**
	 * 根据订单编号获取订单
	 * @author wujunyuan
	 * @date 2018年12月20日  上午10:42:24
	 * @param orderNum
	 * @return
	 */
	@Select("SELECT" + 
			"	o.payType" + 
			" FROM" + 
			"	o_order o" + 
			" WHERE" + 
			"	o.orderNum = #{orderNum}")
	List<Map<String, Object>> findOrderByOrderNum(@Param("orderNum") String orderNum);
	
	/**
	 * 获取订单详情
	 *
	 * @author lihongwu
	 * 2018年12月20日 下午4:30:33
	 * @param orderId
	 * @return
	 */
	@Select("SELECT" + 
			"	o.id AS 'orderId'," + 
			"	o.userId," + 
			"	o.orderNum," + 
			"	o.paymentPrice," + 
			"	o.agentPrice," + 
			"	o.traderPrice," + 
			"	o.totalPrice," + 
			"	o.`grants`," + 
			"	o.userName," + 
			"	o.linkName," + 
			"	o.count," + 
			"	o.remarks," + 
			"	o.payStatus," + 
			"	o.payType," + 
			"	o.placeTime," + 
			"	o.cancelTime," + 
			"	o.payTime," + 
			"	o.update_time," + 
			"	os.addressee," + 
			"	os.addresseePhone," + 
			"	os.address," + 
			"	os.electricName," + 
			"	os.electricPhone," + 
			"	os.electricAddress," + 
			"	os.shippingName," + 
			"	os.shippingCode" + 
			" FROM" + 
			"	o_order o" + 
			" LEFT JOIN o_shipping os ON o.shippingId = os.id" + 
			" WHERE o.id = #{orderId}")
	Map<String,Object> getOrderDetail(@Param("orderId") String orderId);
	
	/**
	 * 获取订单item
	 *
	 * @author lihongwu
	 * 2018年12月20日 下午4:37:40
	 * @param orderId
	 * @return
	 */
	@Select("SELECT " + 
			"	oi.goodsTitle, " + 
//			"	oi.goodsId, " + 
			"	oi.goodsPic, " + 
			"	oi.goodsPrice, " + 
			"IF ( " + 
			"	oi.categoryId = 0 " + 
			"	OR ISNULL(oi.categoryId), " + 
			"	ggs.categoryId, " + 
			"	oi.categoryId " + 
			") AS categoryId, " + 
			" oi.goodsCount " + 
			"FROM " + 
			"	o_order_item oi " + 
			"LEFT JOIN g_goods ggs ON ggs.id = oi.goodsId " + 
			" WHERE oi.orderId = #{orderId}")
	List<Map<String,Object>> getOrderItemByOrderId(@Param("orderId") String orderId);
	
	/**
	 * 获取物流表id
	 * @author chenluqiang
	 * @date 2018年12月25日 上午10:20:59 
	 * @param orderId
	 * @return
	 */
	@Select("SELECT oor.shippingId FROM o_order oor WHERE oor.id = #{orderId}")
	String getShippingIdByOrderId(@Param("orderId") String orderId);


	/**
	 * 
	 * @author wujunyuan
	 * @date 2018年12月25日  下午5:46:13
	 * @param orderId
	 * @return
	 */
	@Select("SELECT " + 
			"	osg.address, " + 
			"	osg.addressee, " + 
			"	osg.addresseePhone, " + 
			"	osg.zipcode, " + 
			"	osg.electricAddress, " + 
			"	osg.electricName, " + 
			"	osg.electricPhone, " + 
			"	osg.receiverName " + 
			"FROM " + 
			"	o_order oor " + 
			"LEFT JOIN o_shipping osg ON oor.shippingId = osg.id " + 
			"WHERE oor.id = #{orderId}")
	Map<String, Object> findOrderAddressById(@Param("orderId") String orderId);


	/**
	 * 查询销售明星列表
	 * @author wujunyuan
	 * @date 2018年12月27日  上午10:45:40
	 * @param time 本日明星=1   本周明星=2
	 * @param userType 电工明星=3   分销明星=2 代理明星=1
	 * @return
	 */
	List<Map<String, Object>> findSalesStar(@Param("time") Integer time, @Param("userType") Integer userType);


	/**
	 * 查找超过一年时间的订单
	 * @author wujunyuan
	 * @param dictType 
	 * @param quartzKeyword 
	 * @date 2019年1月5日  下午5:05:57
	 * @return
	 */
	@Select("SELECT " + 
			"	oor.id AS orderId " + 
			"FROM " + 
			"	o_order oor " + 
			"LEFT JOIN q_quartz qqz ON oor.id = qqz.businessId " + 
			"WHERE " + 
			"	qqz.keyword = #{quartzKeyword} " + 
			"AND DATEDIFF( " + 
			"	DATE_FORMAT(NOW(), '%Y-%m-%d'), " + 
			"	DATE_FORMAT(oor.placeTime, '%Y-%m-%d') " + 
			") >= ( " + 
			"	SELECT " + 
			"		tdt.val " + 
			"	FROM " + 
			"		t_dict tdt " + 
			"	WHERE " + 
			"		tdt.type = #{dictType} " + 
			")")
	List<Map<String, Object>> findOutTimeOrder(@Param("quartzKeyword") String quartzKeyword,
                                               @Param("dictType") String dictType);


	/**
	 * 查找发货15天的订单
	 * @author wujunyuan
	 * @date 2019年1月7日  下午4:44:16
	 * @param quartzKeyword
	 * @param dicttypeGettime
	 * @return
	 */
	@Select("SELECT " + 
			"	oor.id AS orderId, " + 
			"	oor.linkUserId, " + 
			"	oor.`grants` AS sumGrants " + 
			"FROM " + 
			"	o_order oor " + 
			"LEFT JOIN o_process ops ON ops.orderId = oor.id " + 
			"LEFT JOIN q_quartz qqz ON oor.id = qqz.businessId " + 
			"WHERE " + 
			"	qqz.keyword = #{quartzKeyword} " + 
			//能发放补助金
			"	AND ops.isGrants = 0 " + 
			"AND DATEDIFF( " + 
			"	DATE_FORMAT(NOW(), '%Y-%m-%d'), " + 
			"	DATE_FORMAT(ops.sendTime, '%Y-%m-%d') " + 
			") > ( " + 
//			") = 0 " +
			"	SELECT " + 
			"		tdt.val " + 
			"	FROM " + 
			"		t_dict tdt " + 
			"	WHERE " + 
			"		tdt.type = #{dictType} " + 
			") "
			)
	List<Map<String, Object>> findSendOutTimeOrder(@Param("quartzKeyword") String quartzKeyword, @Param("dictType") String dicttypeGettime);

}